﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using TCMS.Models.DBFatory;
using System.Data;
using System.Text;
using System.Web.Mvc;

namespace TCMS.Models
{
    public class TaskDAOImp : TaskDAO
    {
        private MySqlConnection conn = null;
        public static List<TaskEntity> _tasks = null;

        public string sql_getTasksByPlanner = "select * from tasks where createBy = @1;";
        public StringBuilder sql_getTasksByManagerSearch = new StringBuilder("select * from tasks where 1=1");
        public string sql_getSingleTaskByShop = "select * from tasks where shopName = @shopName and status=1;";//get current task
        public string sql_getAlltasksByShop = "select * from tasks where shopName = @shopName and status<>0;";
        public StringBuilder sql_searchTasksByDate = new StringBuilder("select * from tasks where createBy = @creater ");
        public string sql_addNewTask = @"
INSERT INTO TASKS (
        name, shopName, productName, status, batch, startTime, endTime, dangerousProducts, dangerousProcedure, inPopulating, department, team, teamLeader, teamSafety, departSafety, teamTel, createBy, createTime, remark) 
VALUES(@name,@shopName,@productName,2,@batch,@startTime,@endTime,@dangerousProducts,@dangerousProcedure,@inPopulating,@department,@team,@teamLeader,@teamSafety,@departSafety,@teamTel,@createBy,@createTime,@remark);";

        public const string SQL_GET_DEPARTMENTS = @"SELECT DISTINCT department FROM tasks";
        public const string SQL_GET_TEAMS = @"SELECT DISTINCT team FROM tasks";
        public const string sQL_GET_SINGLE_TASK_BY_ID = @"SELECT * FROM tasks WHERE id = @id LIMIT 0, 1";
        
        /// <summary>
        /// gets all tasks created by the specified planner.
        /// </summary>
        /// <param name="planner"></param>
        /// <returns></returns>
        public List<TaskEntity> getTasksByPlanner(string planner)
        {
            _tasks = new List<TaskEntity>();
            conn = DBConnection.GetConnection();
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql_getTasksByPlanner, conn);
                cmd.Parameters.AddWithValue("@1", planner);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    _tasks.Add(new TaskEntity
                    {
                        ID = Convert.ToInt32(rdr[0].ToString()),
                        Name = rdr[1].ToString(),
                        ShopName = rdr[2].ToString(),
                        ProductName = rdr[3].ToString(),
                        Status = Int32.Parse(rdr[4].ToString()),
                        Batch = rdr[5].ToString(),
                        StartTime = DateTime.Parse(rdr[6].ToString()),
                        EndTime = DateTime.Parse(rdr[7].ToString()),
                        DangerousProducts = rdr[8].ToString(),
                        DangerousProcedure = rdr[9].ToString(),
                        InPopulating = Int32.Parse(rdr[10].ToString()),
                        Department = rdr[11].ToString(),
                        Team = rdr[12].ToString(),
                        TeamLeader = rdr[13].ToString(),
                        TeamSafety = rdr[14].ToString(),
                        DepartmentSafety = rdr[15].ToString(),
                        TeamTel = rdr[16].ToString(),
                        CreateBy = rdr[17].ToString(),
                        CreatedTime = DateTime.Parse(rdr[18].ToString()),
                        Remark = rdr[19].ToString()
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return _tasks;
        }

        /// <summary>
        /// gets the current task created in the selected shopfloor 
        /// </summary>
        /// <param name="shopName"></param>
        /// <returns></returns>
        public TaskEntity getSingleTaskByShop(string shopName)
        {
            conn = DBConnection.GetConnection();
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql_getSingleTaskByShop, conn);
                cmd.Parameters.AddWithValue("@shopName", shopName);
                MySqlDataReader rdr = cmd.ExecuteReader();

                if (rdr.Read())
                {
                    return new TaskEntity
                    {
                        ID = Convert.ToInt32(rdr[0].ToString()),
                        Name = rdr[1].ToString(),
                        ShopName = rdr[2].ToString(),
                        ProductName = rdr[3].ToString(),
                        Status = Int32.Parse(rdr[4].ToString()),
                        Batch = rdr[5].ToString(),
                        StartTime = DateTime.Parse(rdr[6].ToString()),
                        EndTime = DateTime.Parse(rdr[7].ToString()),
                        DangerousProducts = rdr[8].ToString(),
                        DangerousProcedure = rdr[9].ToString(),
                        InPopulating = Int32.Parse(rdr[10].ToString()),
                        Department = rdr[11].ToString(),
                        Team = rdr[12].ToString(),
                        TeamLeader = rdr[13].ToString(),
                        TeamSafety = rdr[14].ToString(),
                        DepartmentSafety = rdr[15].ToString(),
                        TeamTel = rdr[16].ToString(),
                        CreateBy = rdr[17].ToString(),
                        CreatedTime = DateTime.Parse(rdr[18].ToString()),
                        Remark = rdr[19].ToString()
                    };
                }

                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// gets all non-completed tasks created in the selected shopfloor 
        /// </summary>
        /// <param name="shopName"></param>
        /// <returns></returns>
        public List<TaskEntity> getAllTasksByShop(string shopName)
        {
            _tasks = new List<TaskEntity>();
            conn = DBConnection.GetConnection();
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql_getAlltasksByShop, conn);
                cmd.Parameters.AddWithValue("@shopName", shopName);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    _tasks.Add(new TaskEntity
                    {
                        ID = Convert.ToInt32(rdr[0].ToString()),
                        Name = rdr[1].ToString(),
                        ShopName = rdr[2].ToString(),
                        ProductName = rdr[3].ToString(),
                        Status = Int32.Parse(rdr[4].ToString()),
                        Batch = rdr[5].ToString(),
                        StartTime = DateTime.Parse(rdr[6].ToString()),
                        EndTime = DateTime.Parse(rdr[7].ToString()),
                        DangerousProducts = rdr[8].ToString(),
                        DangerousProcedure = rdr[9].ToString(),
                        InPopulating = Int32.Parse(rdr[10].ToString()),
                        Department = rdr[11].ToString(),
                        Team = rdr[12].ToString(),
                        TeamLeader = rdr[13].ToString(),
                        TeamSafety = rdr[14].ToString(),
                        DepartmentSafety = rdr[15].ToString(),
                        TeamTel = rdr[16].ToString(),
                        CreateBy = rdr[17].ToString(),
                        CreatedTime = DateTime.Parse(rdr[18].ToString()),
                        Remark = rdr[19].ToString()
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return _tasks;
        }
        /// <summary>
        /// adds a new task created by planner into DB
        /// </summary>
        /// <param name="newTask"></param>
        /// <returns></returns>
        public List<TaskEntity> addNewTask(TaskEntity newTask)
        {
            conn = DBConnection.GetConnection();
            try
            {
                var cmd = new MySqlCommand("add_new_task", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                var p = cmd.Parameters.Add("@shop_name", MySqlDbType.String);
                p.Value = newTask.ShopName;

                var p2 = cmd.Parameters.Add("@start_date", MySqlDbType.DateTime);
                p2.Value = newTask.StartTime;

                var p3 = cmd.Parameters.Add("@end_date", MySqlDbType.DateTime);
                p3.Value = newTask.EndTime;

                var result = cmd.ExecuteScalar();

                if (Int32.Parse(result.ToString()) == 0)
                {
                    //current task can be added into db
                    MySqlCommand cmdInsert = new MySqlCommand(sql_addNewTask, conn);
                    cmdInsert.Parameters.AddWithValue("@name", newTask.Name);
                    cmdInsert.Parameters.AddWithValue("@shopName", newTask.ShopName);
                    cmdInsert.Parameters.AddWithValue("@productName", newTask.ProductName);
                    cmdInsert.Parameters.AddWithValue("@batch", newTask.Batch);
                    cmdInsert.Parameters.AddWithValue("@startTime", newTask.StartTime);
                    cmdInsert.Parameters.AddWithValue("@endTime", newTask.EndTime);
                    cmdInsert.Parameters.AddWithValue("@dangerousProducts", newTask.DangerousProducts);
                    cmdInsert.Parameters.AddWithValue("@dangerousProcedure", newTask.DangerousProcedure);
                    cmdInsert.Parameters.AddWithValue("@inPopulating", newTask.InPopulating);
                    cmdInsert.Parameters.AddWithValue("@department", newTask.Department);
                    cmdInsert.Parameters.AddWithValue("@team", newTask.Team);
                    cmdInsert.Parameters.AddWithValue("@teamLeader", newTask.TeamLeader);
                    cmdInsert.Parameters.AddWithValue("@teamSafety", newTask.TeamSafety);
                    cmdInsert.Parameters.AddWithValue("@departSafety", newTask.DepartmentSafety);
                    cmdInsert.Parameters.AddWithValue("@teamTel", newTask.TeamTel);
                    cmdInsert.Parameters.AddWithValue("@createBy", newTask.CreateBy);
                    cmdInsert.Parameters.AddWithValue("@createTime", DateTime.Now);
                    cmdInsert.Parameters.AddWithValue("@remark", newTask.Remark);

                    cmdInsert.ExecuteNonQuery();
                    return null;

                }
                else
                {
                    //new task is conflict with other tasks, read all non_completed tasks of the shop floor
                    return getAllTasksByShop(newTask.ShopName);

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// gets all tasks created by the specified planner.
        /// </summary>
        /// <param name="planner"></param>
        /// <returns></returns>
        public List<TaskEntity> searchTasksByDate(string planner, DateTime startTime, DateTime endTime)
        {
            _tasks = new List<TaskEntity>();
            conn = DBConnection.GetConnection();
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql_searchTasksByDate.ToString(), conn);
                cmd.Parameters.AddWithValue("@creater", planner);
                cmd.Parameters.AddWithValue("@startTime", startTime);
                cmd.Parameters.AddWithValue("@endTime", endTime);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    _tasks.Add(new TaskEntity
                    {
                        ID = Convert.ToInt32(rdr[0].ToString()),
                        Name = rdr[1].ToString(),
                        ShopName = rdr[2].ToString(),
                        ProductName = rdr[3].ToString(),
                        Status = Int32.Parse(rdr[4].ToString()),
                        Batch = rdr[5].ToString(),
                        StartTime = DateTime.Parse(rdr[6].ToString()),
                        EndTime = DateTime.Parse(rdr[7].ToString()),
                        DangerousProducts = rdr[8].ToString(),
                        DangerousProcedure = rdr[9].ToString(),
                        InPopulating = Int32.Parse(rdr[10].ToString()),
                        Department = rdr[11].ToString(),
                        Team = rdr[12].ToString(),
                        TeamLeader = rdr[13].ToString(),
                        TeamSafety = rdr[14].ToString(),
                        DepartmentSafety = rdr[15].ToString(),
                        TeamTel = rdr[16].ToString(),
                        CreateBy = rdr[17].ToString(),
                        CreatedTime = DateTime.Parse(rdr[18].ToString()),
                        Remark = rdr[19].ToString()
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return _tasks;
        }

        /// <summary>
        /// get tasks by manager's search
        /// </summary>
        /// <param name="department"></param>
        /// <param name="team"></param>
        /// <param name="shopfloor"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public List<TaskEntity> getTasksByManagerSearch(string department, string team, string shopfloor, DateTime startTime, DateTime endTime)
        {
            if (!string.IsNullOrEmpty(department))
                sql_getTasksByManagerSearch.Append(" and department=" + department + ", ");

            if (!string.IsNullOrEmpty(team))
                sql_getTasksByManagerSearch.Append(" and team=" + team + ", ");

            if (!string.IsNullOrEmpty(shopfloor))
                sql_getTasksByManagerSearch.Append(" and shopName=" + shopfloor + ", ");

            if (!string.IsNullOrEmpty(startTime.ToString()))
                sql_getTasksByManagerSearch.Append(" and startTime>=" + startTime + ", ");

            if (!string.IsNullOrEmpty(endTime.ToString()))
                sql_getTasksByManagerSearch.Append(" and endTime<=" + endTime);

            _tasks = new List<TaskEntity>();
            conn = DBConnection.GetConnection();
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql_getTasksByManagerSearch.ToString(), conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    _tasks.Add(new TaskEntity
                    {
                        ID = Convert.ToInt32(rdr[0].ToString()),
                        Name = rdr[1].ToString(),
                        ShopName = rdr[2].ToString(),
                        ProductName = rdr[3].ToString(),
                        Status = Int32.Parse(rdr[4].ToString()),
                        Batch = rdr[5].ToString(),
                        StartTime = DateTime.Parse(rdr[6].ToString()),
                        EndTime = DateTime.Parse(rdr[7].ToString()),
                        DangerousProducts = rdr[8].ToString(),
                        DangerousProcedure = rdr[9].ToString(),
                        InPopulating = Int32.Parse(rdr[10].ToString()),
                        Department = rdr[11].ToString(),
                        Team = rdr[12].ToString(),
                        TeamLeader = rdr[13].ToString(),
                        TeamSafety = rdr[14].ToString(),
                        DepartmentSafety = rdr[15].ToString(),
                        TeamTel = rdr[16].ToString(),
                        CreateBy = rdr[17].ToString(),
                        CreatedTime = DateTime.Parse(rdr[18].ToString()),
                        Remark = rdr[19].ToString()
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return _tasks;
        }

        public IEnumerable<string> GetDepartments()
        {
            conn = DBConnection.GetConnection();
            var departments = new List<string>();
            try
            {
                MySqlCommand cmd = new MySqlCommand(SQL_GET_DEPARTMENTS, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (!rdr.IsDBNull(0))
                    {
                        departments.Add(rdr.GetString(0));
                    }
                }
            }
            catch (Exception ex)
            {
                //TODO: log
                //throw ex;
            }
            finally
            {
                conn.Close();
            }
            return departments;
        }

        public IEnumerable<string> GetTeams()
        {
            conn = DBConnection.GetConnection();
            var teams = new List<string>();
            try
            {
                MySqlCommand cmd = new MySqlCommand(SQL_GET_TEAMS, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (!rdr.IsDBNull(0))
                    {
                        teams.Add(rdr.GetString(0));
                    }
                }
            }
            catch (Exception ex)
            {
                //TODO: log
                //throw ex;
            }
            finally
            {
                conn.Close();
            }
            return teams;
        }

        /// <summary>
        /// search
        /// </summary>
        /// <param name="department"></param>
        /// <param name="team"></param>
        /// <param name="shopFloor"></param>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        [HttpGet]
        public List<TaskEntity> Search(int? page, int? pagesize, string department, string team, string shopFloor, DateTime? from, DateTime? to)
        {
            _tasks = new List<TaskEntity>();
            conn = DBConnection.GetConnection();

            string sql = "SELECT * FROM tasks";

            var ors = new List<string>();

            if (!string.IsNullOrEmpty(department))
            {
                ors.Add(string.Format("department='{0}'", department));
            }

            if (!string.IsNullOrEmpty(team))
            {
                ors.Add(string.Format("team='{0}'", team));
            }

            if (!string.IsNullOrEmpty(shopFloor))
            {
                ors.Add(string.Format("shopName='{0}'", shopFloor));
            }

            if (from != null && to != null)
            {
                ors.Add(string.Format("(startTime>='{0}' AND endTime <='{1}')", from, to));
            }

            var ands = new List<string>();
            if (ors.Count > 0)
            {
                ands.Add(string.Format("({0})", string.Join(" AND ", ors)));
            }

            if (HttpContext.Current.User.IsInRole("staff"))
            {
                ands.Add(string.Format("createBy='{0}'", HttpContext.Current.User.Identity.Name));
            }

            if (ands.Count > 0)
            {
                sql += " WHERE " + string.Join(" AND ", ands);
            }

            if (page != null && pagesize != null)
            {
                sql += string.Format(" LIMIT {0}, {1}", (page - 1) * pagesize, pagesize);
            }

            try
            {
                var cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    _tasks.Add(new TaskEntity
                    {
                        ID = Convert.ToInt32(rdr[0].ToString()),
                        Name = rdr[1].ToString(),
                        ShopName = rdr[2].ToString(),
                        ProductName = rdr[3].ToString(),
                        Status = Int32.Parse(rdr[4].ToString()),
                        Batch = rdr[5].ToString(),
                        StartTime = DateTime.Parse(rdr[6].ToString()),
                        EndTime = DateTime.Parse(rdr[7].ToString()),
                        DangerousProducts = rdr[8].ToString(),
                        DangerousProcedure = rdr[9].ToString(),
                        InPopulating = Int32.Parse(rdr[10].ToString()),
                        Department = rdr[11].ToString(),
                        Team = rdr[12].ToString(),
                        TeamLeader = rdr[13].ToString(),
                        TeamSafety = rdr[14].ToString(),
                        DepartmentSafety = rdr[15].ToString(),
                        TeamTel = rdr[16].ToString(),
                        CreateBy = rdr[17].ToString(),
                        CreatedTime = DateTime.Parse(rdr[18].ToString()),
                        Remark = rdr[19].ToString()
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return _tasks;
        }


        public TaskEntity GetSingleTaskById(int id)
        {
            conn = DBConnection.GetConnection();
            try
            {
                MySqlCommand cmd = new MySqlCommand(sQL_GET_SINGLE_TASK_BY_ID, conn);
                cmd.Parameters.AddWithValue("@id", id);
                MySqlDataReader rdr = cmd.ExecuteReader();

                if (rdr.Read())
                {
                    return new TaskEntity
                    {
                        ID = Convert.ToInt32(rdr[0].ToString()),
                        Name = rdr[1].ToString(),
                        ShopName = rdr[2].ToString(),
                        ProductName = rdr[3].ToString(),
                        Status = Int32.Parse(rdr[4].ToString()),
                        Batch = rdr[5].ToString(),
                        StartTime = DateTime.Parse(rdr[6].ToString()),
                        EndTime = DateTime.Parse(rdr[7].ToString()),
                        DangerousProducts = rdr[8].ToString(),
                        DangerousProcedure = rdr[9].ToString(),
                        InPopulating = Int32.Parse(rdr[10].ToString()),
                        Department = rdr[11].ToString(),
                        Team = rdr[12].ToString(),
                        TeamLeader = rdr[13].ToString(),
                        TeamSafety = rdr[14].ToString(),
                        DepartmentSafety = rdr[15].ToString(),
                        TeamTel = rdr[16].ToString(),
                        CreateBy = rdr[17].ToString(),
                        CreatedTime = DateTime.Parse(rdr[18].ToString()),
                        Remark = rdr[19].ToString()
                    };
                }

                return null;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}